---
title:  How to Run a Geode Cache Transaction that Coordinates with an External Database
---

<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements.  See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License.  You may obtain a copy of the License at

     http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->

Coordinate a Geode cache transaction with an external database by using CacheWriter/CacheListener and TransactionWriter/TransactionListener plug-ins, **to provide an alternative to using JTA transactions**.

There are a few things you should be careful about while working with Geode cache transactions and external databases:

-   When you set up the JDBC connection, make sure that auto-commit is disabled. For example, in Java:

    ``` pre
    Connection getConnection() throws SQLException {
        Connection con = ... // create the connection
        con.setAutoCommit(false);
        return con;
    }
    ```

-   The BEGIN statement, database operations and the PREPARE statement must all happen in the same connection session. In order to accomplish this, you will need to obtain the same JDBC connection session across multiple CacheWriter and TransactionWriter/TransactionListener invocations. One way to do this would be to look up the connection (from a user managed Map) based on `cacheTransactionManager.getTransactionId()`.
-   Make sure that the prepare transaction feature is enabled in your external database. It is disabled in PostgreSQL by default. In PostgreSQL, the following property must be modified to enable it:

    ``` pre
    max_prepared_transactions = 1 # 1 or more enables, zero (default) disables this feature.
    ```

Use the following procedure to write a Geode cache transaction that coordinates with an external database:

1.  Configure Geode regions as necessary as described in [How to Run a Geode Cache Transaction](run_a_cache_transaction.html#task_f15_mr3_5k).
2.  Begin the transaction.
3.  If you have not previously committed a previous transaction in this connection, start a database transaction by issuing a BEGIN statement.
4.  Perform Geode cache operations; each cache operation invokes the CacheWriter. Implement the CacheWriter to perform the corresponding external database operations.
5.  Commit the transaction.
    At this point, the TransactionWriter is invoked. The TransactionWriter returns a TransactionEvent, which contains all the operations in the transaction. Call PREPARE TRANSACTION within your TransactionWriter code.

6.  After a transaction is successfully committed in Geode, the TransactionListener is invoked. The TransactionListener calls COMMIT PREPARED to commit the database transaction.


